/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Models.DAO;

/**
 *
 * @author Don
 */
import DBConnection.ConnectionFactory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import Models.Admin;
import Models.*;
import Models.Member;

public class Member_DAO {
    
     
    public ArrayList<ECA> getAllECArecognized(int id)
     {
         System.out.print("getAllECA");
         ECA eca;
         ArrayList<ECA> ecaList = null;
         
         try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT eca.ecaid, eca.name, eca.date, eca.time, eca.points, eca.status, eca.deletestatus FROM eca_attendance eatt RIGHT OUTER JOIN extra_curricular_activities eca ON eatt.ecaid = eca.ecaid where status = 'Recognized' and eca.deletestatus != 1 and (memberid IS NULL OR (memberid != ?)) and eca.ecaid not in (select ecaid from eca_attendance where memberid = ?);");
             
             int i = 1;
           
             pstmt.setInt(i++, id);
             pstmt.setInt(i++, id);
             ResultSet rs = pstmt.executeQuery();
             
             ecaList = new ArrayList<ECA>();
             
             while(rs.next())
             {
                 eca = new ECA();
   
                 eca.setEcaid(rs.getInt("eca.ecaid"));
                 eca.setName(rs.getString("eca.name"));
                 eca.setDate(rs.getDate("eca.date"));
                 eca.setTime(rs.getTime("eca.time"));
                 eca.setPoints(rs.getFloat("eca.points"));
                 //eca.setEcaClassID(rs.getInt("eca.ecaClassID"));
                 eca.setStatus(rs.getString("eca.status"));

                 
                 ecaList.add(eca);
             }
             System.out.print(ecaList);
             return ecaList;
         }        
         catch(Exception e){
         System.out.print(e);
            return ecaList;
         }
         finally{
             return ecaList;
         }
         
         
     }
    
    public ArrayList<ECA> getAllCurrent(int memberid)
     {
         System.out.print("getAllCurrent");
         ECA eca;
         ArrayList<ECA> ecaList = null;
         
         try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT eca.name, ea.time, ea.date, eca.status FROM eca_attendance ea, extra_curricular_activities eca where ea.ecaid = eca.ecaid and memberid = ? ");
             
             int i = 1;
           
             pstmt.setInt(i++, memberid);
             ResultSet rs = pstmt.executeQuery();
             
             ecaList = new ArrayList<ECA>();
             
             while(rs.next())
             {
                 eca = new ECA();
                 
                eca.setName(rs.getString("name"));
                eca.setDate(rs.getDate("date"));
                eca.setTime(rs.getTime("time"));
                eca.setStatus(rs.getString("status"));
         
                 ecaList.add(eca);
             }
             System.out.print(ecaList);
             return ecaList;
         }        
         catch(Exception e){
         System.out.print(e);
            return ecaList;
         }
         finally{
             return ecaList;
         }
         
         
     }
    
    public boolean addECA(ECA eca){
        try{

            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            int i = 1;
            
            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO extra_curricular_activities(name, date, time, points, status) VALUES(?,?,?,?,?)");
            pstmt.setString(i++, eca.getName());
            pstmt.setDate(i++, eca.getDate());
            pstmt.setTime(i++, eca.getTime());
            pstmt.setFloat(i++, eca.getPoints());
            pstmt.setString(i++, eca.getStatus());
            
            pstmt.executeUpdate();
       

            return true;
        }
        catch(Exception e){
            System.out.print(e);
            return false;
        }
    }
    
    public boolean addECAtoUser(ECA_Attendance eca){
        try{

            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            int i = 1;
            
            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO eca_attendance(ecaid, date, time, memberid) VALUES(?,?,?,?)");
            
            pstmt.setInt(i++, eca.getEcaid());
            pstmt.setDate(i++, eca.getDate());
            pstmt.setTime(i++, eca.getTime());
            pstmt.setInt(i++, eca.getMemberid());
            
            pstmt.executeUpdate();
       
            return true;
        }
        catch(Exception e){
            System.out.print(e);
            return false;
        }
    }
    
    
     public ArrayList<Member> getMemberProfile(int id)
     {
         System.out.print("getAllMembers");
         Member member;
         ArrayList<Member> memberList = null;
         
         try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM member where memberid = ? ORDER BY lastname ASC");
             
             PreparedStatement pstpoints = conn.prepareStatement("SELECT SUM(eca.points) as a from extra_curricular_activities eca, eca_attendance ea where ea.ecaid = eca.ecaid and memberid = ?");
             
             int i = 1, j = 1;
             pstmt.setInt(i++, id);
             pstpoints.setInt(j++, id);
             ResultSet rs = pstmt.executeQuery();
             ResultSet rspoint = pstpoints.executeQuery();
             memberList = new ArrayList<Member>();
             
             while(rs.next() && rspoint.next())
             {
                 member = new Member();
                 member.setStatus(rs.getString("status"));
                 member.setCgpa(rs.getFloat("cgpa"));
                 member.setMemberid(rs.getInt("memberid"));
                 member.setBirthdate(rs.getDate("birthdate"));
                 member.setCourse(rs.getString("course"));
                 member.setBatch(rs.getString("batch"));
                 member.setPhone_number(rs.getString("phone_number"));
                 member.setCell_number(rs.getString("cell_number"));
                 member.setFirstname(rs.getString("firstname"));
                 member.setEmail(rs.getString("email"));
                 member.setLastname(rs.getString("lastname"));
                 member.setFullname(rs.getString("firstname") + " " + rs.getString("lastname"));
                 member.setMemberid(rs.getInt("memberid"));
                 member.setEca_points(rspoint.getFloat(1));
                 
                 
                
                 memberList.add(member);
             }
             System.out.print(memberList);
             return memberList;
         }        
         catch(Exception e){
         System.out.print(e);
            return memberList;
         }
         finally{
             return memberList;
         }
         
         
     }
     public boolean editMember(Member member){
        try{

            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            int i = 1;
            
            PreparedStatement pstmt = conn.prepareStatement("UPDATE member SET password = ?, nickname = ?, birthdate = ?, course = ?, phone_number = ?, cell_number = ?, email = ? WHERE memberid = ?");
            
            pstmt.setString(i++, member.getPassword());
            pstmt.setString(i++, member.getNickname());
            pstmt.setDate(i++, member.getBirthdate());
            pstmt.setString(i++, member.getCourse());
            pstmt.setString(i++, member.getPhone_number());
            pstmt.setString(i++, member.getCell_number());
            pstmt.setString(i++, member.getEmail());
            pstmt.setInt(i++, member.getMemberid());
            
            pstmt.executeUpdate();

            return true;
        }
        catch(Exception e){
            System.out.print(e);
            return false;
        }
    }
     public ArrayList <Member> getTotalECAPoints(int id){
         
          Member member;
            ArrayList<Member>  memList =null;
     try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
           
            PreparedStatement pstmt = conn.prepareStatement("SELECT SUM(eca.points) as a from extra_curricular_activities eca, eca_attendance ea where ea.ecaid = eca.ecaid and memberid = ?");
             int i = 1;
   
             pstmt.setInt(i++, id);
             ResultSet rs = pstmt.executeQuery();
             
             memList = new ArrayList<Member>();
             
             while(rs.next())
             {
                 member = new Member();
                 member.setEca_points(rs.getFloat("a"));
                
                 memList.add(member);
             }
             System.out.print(memList);
             return memList;
         }        
         catch(Exception e){
         System.out.print(e);
            return memList;
         }
         finally{
             return memList;
         }
     
}
      public ArrayList<Member> getOtherMembers(int memid)
     {
         System.out.print("getAllMembers");
         Member member;
         ArrayList<Member> memberList = null;
         
         try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM member where memberid != ? ORDER BY lastname ASC");
             
             int i = 1;
             pstmt.setInt(i++, memid);
             
             ResultSet rs = pstmt.executeQuery();
             
             memberList = new ArrayList<Member>();
             
             while(rs.next())
             {
                 member = new Member();
                 
                 member.setFirstname(rs.getString("firstname"));
                 
                 member.setLastname(rs.getString("lastname"));
                 
                 member.setFullname(rs.getString("firstname") + " " + rs.getString("lastname"));
                 member.setMemberid(rs.getInt("memberid"));
                 
                 member.setCourse(rs.getString("course"));
                 
                 member.setBatch(rs.getString("batch"));
                 member.setStatus(rs.getString("status"));
                 memberList.add(member);
             }
             System.out.print(memberList);
             return memberList;
         }        
         catch(Exception e){
         System.out.print(e);
            return memberList;
         }
         finally{
             return memberList;
         }
         
         
     }
      
      public ArrayList<ECA> getAllECArecognized2(String id)
     {
         System.out.print("getAllECA");
         ECA eca;
         ArrayList<ECA> ecaList = null;
         
         try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT eca.ecaid, eca.name, eca.date, eca.time, eca.points, eca.status, eca.deletestatus FROM eca_attendance eatt RIGHT OUTER JOIN extra_curricular_activities eca ON eatt.ecaid = eca.ecaid where status = 'Recognized' and eca.deletestatus != 1 and (memberid IS NULL OR (memberid != ?)) and eca.ecaid not in (select ecaid from eca_attendance where memberid = ?);");
             
             int i = 1;
           
             pstmt.setString(i++, id);
             pstmt.setString(i++, id);
             ResultSet rs = pstmt.executeQuery();
             
             ecaList = new ArrayList<ECA>();
             
             while(rs.next())
             {
                 eca = new ECA();
   
                 eca.setEcaid(rs.getInt("eca.ecaid"));
                 eca.setName(rs.getString("eca.name"));
                 eca.setDate(rs.getDate("eca.date"));
                 eca.setTime(rs.getTime("eca.time"));
                 eca.setPoints(rs.getFloat("eca.points"));
                 //eca.setEcaClassID(rs.getInt("eca.ecaClassID"));
                 eca.setStatus(rs.getString("eca.status"));

                 
                 ecaList.add(eca);
             }
             System.out.print(ecaList);
             return ecaList;
         }        
         catch(Exception e){
         System.out.print(e);
            return ecaList;
         }
         finally{
             return ecaList;
         }
         
         
     }
}
